Blocked processes are often a big problem to DBA’s. They are difficult to monitor. We receive desperate calls saying “Everything is slow!” and before we can do anything a second call “Oh, don’t worry, it’s everything ok again”.
Most of times it happens because ill-behaved process that blocks other tasks for short periods. How can we find this kind of problem?
SQL Server has a server configuration called Blocked Process Threshold. We can set a value, in milliseconds, and SQL Server will generate a report every time a process is blocked by this amount of time.
To configure ‘Blocked Process Threshold’ we can use the following code:
|
1 2 3 |
EXEC sp_configure 'Blocked Process Threshold', 5 RECONFIGURE go |
In this example one Blocked Process report will be generated every time one process is blocked for more the five seconds. You need to adapt this value to your servers.
There are a few ways to capture blocked process report:
- SQL Profiler: SQL Profiler has an event called ‘Blocked Process Report’ especially to capture this information.
- Extended Events: XE also has a ‘Blocked Process Report’ event.
- Alerts: It’s possible to generate an alert over blocked process reports and generate an email as alert response.
You can create an extended events session to capture the blocked process report:
|
1 2 3 4 5 6 7 8 9 |
-- Create the session CREATE EVENT SESSION [Blocked] ON SERVER ADD EVENT sqlserver.blocked_process_report ADD TARGET package0.event_file (SET filename=N'C:\xel\blocked.xel') -- You need to change the path or create this folder GO -- Start the session ALTER EVENT SESSION [Blocked] ON SERVER STATE = start; GO |
After creating and starting the session, we can query the reports captured by this session. We need to use the DMF (Dynamic Management Function) sys.fn_xe_file_target_read_file to read the session information, but the main information is returned as XML field, so we will need to extract the information from the XML using XML functions.
The query to retrieve the report will be this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
select theNodes.event_data.value('(//blocked-process/process)[1]/@spid','int') as blocking_process, theNodes.event_data.value('(//blocked-process/process/inputbuf)[1]','varchar(max)') as blocking_text, theNodes.event_data.value('(//blocked-process/process)[1]/@clientapp','varchar(100)') as blocking_app, theNodes.event_data.value('(//blocked-process/process)[1]/@loginname','varchar(50)') as blocking_login, theNodes.event_data.value('(//blocked-process/process)1]/@isolationlevel','varchar(50)') as blocking_isolation, theNodes.event_data.value('(//blocked-process/process)[1]/@hostname','varchar(50)') as blocking_host, theNodes.event_data.value('(//blocking-process/process)[1]/@spid','int') as blocked_process, theNodes.event_data.value('(//blocking-process/process/inputbuf)[1]','varchar(max)') as blocked_text, theNodes.event_data.value('(//blocking-process/process)[1]/@clientapp','varchar(100)') as blocked_app, theNodes.event_data.value('(//blocking-process/process)[1]/@loginname','varchar(50)') as blocked_login, theNodes.event_data.value('(//blocked-process/process)[1]/@isolationlevel','varchar(50)') as blocked_isolation, theNodes.event_data.value('(//blocking-process/process)[1]/@hostname','varchar(50)') as blocked_host from (select convert(xml,event_data) event_data from sys.fn_xe_file_target_read_file('c:\xel\blocked*.xel', NULL, NULL, NULL)) theData cross apply theData.event_data.nodes('//event') theNodes(event_data) |
Notice that extended events hasn’t a solution to notify about the reports. You can achieve a blocked process notification solution using an alert.
SQL Server has a performance counter called Process Blocked that counts the number of blocked process according to ‘Blocked Process Threshold’ configuration. We can create an alert over this counter and configure the notification to send an e-mail to the operator.
The script to create the alert will be this:
|
1 2 |
-- Create the alert EXEC msdb.dbo.sp_add_alert @name=N'Blocked Process Alert', @enabled=1, @category_name=N'[Uncategorized]', @performance_condition=N'General Statistics|Processes blocked||>|0' GO |
— Add one e-mail notification to one operator
|
1 2 |
EXEC msdb.dbo.sp_add_notification @alert_name = N'Blocked Process Alert', @operator_name = N'Fulano' -- You need to configure the operator first , @notification_method = 1; GO |
Redgate SQL Monitor also has an alert called ‘Blocked Process’ with some differences:
- It doesn’t requires the ‘Blocked Process Threshold’ to be configured on the server
- The information is retrieved by the server that holds SQL Monitor, from several different SQL Servers
- We can do one single configuration for the alert to check several servers
- It has a notification solution, so you can receive notifications by e-mail.
Load comments